{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7e51e16e-d3b3-499c-9111-9fad22425010",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       员工编号  年龄 性别     学历 婚姻状况   部门 本月入转调    年龄段  Unnamed: 8 Unnamed: 9  \\\n",
      "0  80010030  41  女     专科   单身  销售部    入职   40=<         NaN       开始数值   \n",
      "1  80004349  49  男   专科以下   已婚  研发部   NaN   40=<         NaN         18   \n",
      "2  80002344  37  男     专科   单身  研发部   NaN  35-39         NaN         25   \n",
      "3  80001588  33  女  硕士研究生   已婚  研发部   NaN  30-34         NaN         30   \n",
      "4  80009001  27  男   专科以下   已婚  研发部   NaN  25-29         NaN         35   \n",
      "\n",
      "  Unnamed: 10  Unnamed: 11    维度    差值  \n",
      "0        年龄范围          NaN   总人数  1470  \n",
      "1       18-24          NaN    年龄     0  \n",
      "2       25-29          NaN    性别     0  \n",
      "3       30-34          NaN    学历     0  \n",
      "4       35-39          NaN  婚姻状况     0  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 文件路径\n",
    "file_path = r\"D:\\Users\\方睿隆\\Desktop\\数据分析可视化\\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\\第四章 人力资源可视化看板.xlsx\"\n",
    "\n",
    "# 读取数据\n",
    "data = pd.read_excel(file_path, sheet_name=\"202203人员基础信息\")\n",
    "\n",
    "# 查看前几行数据\n",
    "print(data.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fa0c587a-dd4d-437d-8f8d-5e698aa74ccb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1470 entries, 0 to 1469\n",
      "Data columns (total 14 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   员工编号         1470 non-null   int64  \n",
      " 1   年龄           1470 non-null   int64  \n",
      " 2   性别           1470 non-null   object \n",
      " 3   学历           1470 non-null   object \n",
      " 4   婚姻状况         1470 non-null   object \n",
      " 5   部门           1470 non-null   object \n",
      " 6   本月入转调        50 non-null     object \n",
      " 7   年龄段          1470 non-null   object \n",
      " 8   Unnamed: 8   0 non-null      float64\n",
      " 9   Unnamed: 9   22 non-null     object \n",
      " 10  Unnamed: 10  22 non-null     object \n",
      " 11  Unnamed: 11  0 non-null      float64\n",
      " 12  维度           22 non-null     object \n",
      " 13  差值           22 non-null     object \n",
      "dtypes: float64(2), int64(2), object(10)\n",
      "memory usage: 160.9+ KB\n",
      "None\n",
      "               员工编号           年龄  Unnamed: 8  Unnamed: 11\n",
      "count  1.470000e+03  1470.000000         0.0          0.0\n",
      "mean   8.000594e+07    36.923810         NaN          NaN\n",
      "std    2.938656e+03     9.135373         NaN          NaN\n",
      "min    8.000100e+07    18.000000         NaN          NaN\n",
      "25%    8.000326e+07    30.000000         NaN          NaN\n",
      "50%    8.000594e+07    36.000000         NaN          NaN\n",
      "75%    8.000853e+07    43.000000         NaN          NaN\n",
      "max    8.001098e+07    60.000000         NaN          NaN\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "# 查看数据类型和缺失值\n",
    "print(data.info())\n",
    "\n",
    "# 描述统计\n",
    "print(data.describe())\n",
    "\n",
    "# 检查是否有重复值\n",
    "print(data.duplicated().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "11ad74ed-dfd8-4b13-8858-678aef08d23a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 填充缺失值（如用最常见值填充）\n",
    "data['学历'] = data['学历'].fillna(data['学历'].mode()[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d2c9376e-2404-4ea4-a228-2b70a43f0ff0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 删除重复值\n",
    "data = data.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "acce369a-c24c-4abf-82e1-6588625ba7b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['学历'] = data['学历'].replace({'本科生': '本科', '研究生': '硕士'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a65afc19-db08-4a85-a90c-8ec7948df0be",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 添加年龄段\n",
    "bins = [18, 25, 35, 45, 60]\n",
    "labels = ['18-24', '25-34', '35-44', '45-59']\n",
    "data['年龄段'] = pd.cut(data['年龄'], bins=bins, labels=labels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "79739210-d9b8-4b3b-b696-0afcb6297a5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 性别比例\n",
    "gender_stats = data['性别'].value_counts(normalize=True).reset_index()\n",
    "gender_stats.columns = ['性别', '比例']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7cb176ef-336c-4d94-ba3e-c36c7135d0b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 年龄段分部\n",
    "age_stats = data['年龄段'].value_counts().reset_index()\n",
    "age_stats.columns = ['年龄段', '人数']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "20721ae0-cfb1-4e31-a6a6-90fa96e6ea55",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 部门员工人数\n",
    "dept_stats = data['部门'].value_counts().reset_index()\n",
    "dept_stats.columns = ['部门', '人数']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "95bbeafe-b6bf-41c4-ba72-8f5c35c67ad4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 学历分布\n",
    "edu_stats = data['学历'].value_counts().reset_index()\n",
    "edu_stats.columns = ['学历', '人数']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d34d4b2a-970f-43c2-baf8-39e6d418ac5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 人员变动统计\n",
    "status_stats = data['本月入转调'].value_counts().reset_index()\n",
    "status_stats.columns = ['状态', '人数']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "29a9003b-4b85-4ae1-942c-b29f22d4193a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 性别比例\n",
    "import plotly.express as px\n",
    "\n",
    "gender_stats = data['性别'].value_counts().reset_index()\n",
    "gender_stats.columns = ['性别', '人数']\n",
    "\n",
    "fig_gender = px.pie(\n",
    "    gender_stats,\n",
    "    names='性别',\n",
    "    values='人数',\n",
    "    title='员工性别比例',\n",
    "    hole=0.4,  # 环形图\n",
    "    color_discrete_sequence=px.colors.sequential.Blues\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e2327b25-acc8-4842-8f87-4e09f24f6902",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 学历分布\n",
    "edu_stats = data['学历'].value_counts().reset_index()\n",
    "edu_stats.columns = ['学历', '人数']\n",
    "\n",
    "fig_edu = px.bar(\n",
    "    edu_stats,\n",
    "    x='人数',\n",
    "    y='学历',\n",
    "    orientation='h',\n",
    "    title='员工学历分布',\n",
    "    color='人数',\n",
    "    color_continuous_scale=px.colors.sequential.Greens\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "8e80a93c-9607-4064-bbe5-5be7dd37ade0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 人员变动情况\n",
    "status_stats = data['本月入转调'].value_counts().reset_index()\n",
    "status_stats.columns = ['状态', '人数']\n",
    "\n",
    "fig_status = px.bar(\n",
    "    status_stats,\n",
    "    x='状态',\n",
    "    y='人数',\n",
    "    title='本月人员变动情况',\n",
    "    color='人数',\n",
    "    color_continuous_scale=px.colors.sequential.Oranges\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "26765732-8f27-462e-b40c-12c7c383ef03",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting dash\n",
      "  Downloading dash-2.18.2-py3-none-any.whl.metadata (10 kB)\n",
      "Requirement already satisfied: Flask<3.1,>=1.0.4 in d:\\anaconda\\lib\\site-packages (from dash) (3.0.3)\n",
      "Requirement already satisfied: Werkzeug<3.1 in d:\\anaconda\\lib\\site-packages (from dash) (3.0.3)\n",
      "Requirement already satisfied: plotly>=5.0.0 in d:\\anaconda\\lib\\site-packages (from dash) (5.24.1)\n",
      "Collecting dash-html-components==2.0.0 (from dash)\n",
      "  Downloading dash_html_components-2.0.0-py3-none-any.whl.metadata (3.8 kB)\n",
      "Collecting dash-core-components==2.0.0 (from dash)\n",
      "  Downloading dash_core_components-2.0.0-py3-none-any.whl.metadata (2.9 kB)\n",
      "Collecting dash-table==5.0.0 (from dash)\n",
      "  Downloading dash_table-5.0.0-py3-none-any.whl.metadata (2.4 kB)\n",
      "Requirement already satisfied: importlib-metadata in d:\\anaconda\\lib\\site-packages (from dash) (7.0.1)\n",
      "Requirement already satisfied: typing-extensions>=4.1.1 in d:\\anaconda\\lib\\site-packages (from dash) (4.11.0)\n",
      "Requirement already satisfied: requests in d:\\anaconda\\lib\\site-packages (from dash) (2.32.3)\n",
      "Collecting retrying (from dash)\n",
      "  Downloading retrying-1.3.4-py3-none-any.whl.metadata (6.9 kB)\n",
      "Requirement already satisfied: nest-asyncio in d:\\anaconda\\lib\\site-packages (from dash) (1.6.0)\n",
      "Requirement already satisfied: setuptools in d:\\anaconda\\lib\\site-packages (from dash) (75.1.0)\n",
      "Requirement already satisfied: Jinja2>=3.1.2 in d:\\anaconda\\lib\\site-packages (from Flask<3.1,>=1.0.4->dash) (3.1.4)\n",
      "Requirement already satisfied: itsdangerous>=2.1.2 in d:\\anaconda\\lib\\site-packages (from Flask<3.1,>=1.0.4->dash) (2.2.0)\n",
      "Requirement already satisfied: click>=8.1.3 in d:\\anaconda\\lib\\site-packages (from Flask<3.1,>=1.0.4->dash) (8.1.7)\n",
      "Requirement already satisfied: blinker>=1.6.2 in d:\\anaconda\\lib\\site-packages (from Flask<3.1,>=1.0.4->dash) (1.6.2)\n",
      "Requirement already satisfied: tenacity>=6.2.0 in d:\\anaconda\\lib\\site-packages (from plotly>=5.0.0->dash) (8.2.3)\n",
      "Requirement already satisfied: packaging in d:\\anaconda\\lib\\site-packages (from plotly>=5.0.0->dash) (24.1)\n",
      "Requirement already satisfied: MarkupSafe>=2.1.1 in d:\\anaconda\\lib\\site-packages (from Werkzeug<3.1->dash) (2.1.3)\n",
      "Requirement already satisfied: zipp>=0.5 in d:\\anaconda\\lib\\site-packages (from importlib-metadata->dash) (3.17.0)\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in d:\\anaconda\\lib\\site-packages (from requests->dash) (3.3.2)\n",
      "Requirement already satisfied: idna<4,>=2.5 in d:\\anaconda\\lib\\site-packages (from requests->dash) (3.7)\n",
      "Requirement already satisfied: urllib3<3,>=1.21.1 in d:\\anaconda\\lib\\site-packages (from requests->dash) (2.2.3)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in d:\\anaconda\\lib\\site-packages (from requests->dash) (2024.8.30)\n",
      "Requirement already satisfied: six>=1.7.0 in d:\\anaconda\\lib\\site-packages (from retrying->dash) (1.16.0)\n",
      "Requirement already satisfied: colorama in d:\\anaconda\\lib\\site-packages (from click>=8.1.3->Flask<3.1,>=1.0.4->dash) (0.4.6)\n",
      "Downloading dash-2.18.2-py3-none-any.whl (7.8 MB)\n",
      "   ---------------------------------------- 0.0/7.8 MB ? eta -:--:--\n",
      "   ---------------------------------------- 0.0/7.8 MB ? eta -:--:--\n",
      "   -- ------------------------------------- 0.5/7.8 MB 3.4 MB/s eta 0:00:03\n",
      "   ------------- -------------------------- 2.6/7.8 MB 7.9 MB/s eta 0:00:01\n",
      "   ---------------------------------------- 7.8/7.8 MB 15.6 MB/s eta 0:00:00\n",
      "Downloading dash_core_components-2.0.0-py3-none-any.whl (3.8 kB)\n",
      "Downloading dash_html_components-2.0.0-py3-none-any.whl (4.1 kB)\n",
      "Downloading dash_table-5.0.0-py3-none-any.whl (3.9 kB)\n",
      "Downloading retrying-1.3.4-py3-none-any.whl (11 kB)\n",
      "Installing collected packages: dash-table, dash-html-components, dash-core-components, retrying, dash\n",
      "Successfully installed dash-2.18.2 dash-core-components-2.0.0 dash-html-components-2.0.0 dash-table-5.0.0 retrying-1.3.4\n"
     ]
    }
   ],
   "source": [
    "!pip install dash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "5f9c83bd-dd2e-4137-a136-45509fff039c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"650\"\n",
       "            src=\"http://127.0.0.1:8050/\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "            \n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x1a7abe15070>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from dash import Dash, html, dcc\n",
    "\n",
    "# 初始化 Dash 应用\n",
    "app = Dash(__name__)\n",
    "\n",
    "# 应用布局\n",
    "app.layout = html.Div(\n",
    "    style={'backgroundColor': '#1c2e4a', 'color': 'white', 'padding': '20px'},\n",
    "    children=[\n",
    "        html.H1('人力资源数据可视化看板', style={'textAlign': 'center', 'color': 'white'}),\n",
    "        html.Div(\n",
    "            [\n",
    "                dcc.Graph(figure=fig_gender, style={'width': '48%', 'display': 'inline-block'}),\n",
    "                dcc.Graph(figure=fig_edu, style={'width': '48%', 'display': 'inline-block'}),\n",
    "            ]\n",
    "        ),\n",
    "        html.Div(\n",
    "            [\n",
    "                dcc.Graph(figure=fig_status, style={'width': '100%', 'display': 'inline-block'}),\n",
    "            ]\n",
    "        ),\n",
    "    ]\n",
    ")\n",
    "\n",
    "# 运行应用\n",
    "if __name__ == '__main__':\n",
    "    app.run_server(debug=True, use_reloader=False)\n",
    "\n",
    "# 运行后可在http://127.0.0.1:8050查看可视化面板"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2ce92e9-a043-4166-b1c3-0e5647e417a8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
